<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:lzc="http://schemas.icitadel.com/Lazarus/Compiled.xsd" version="1.0">

	<xsl:output method="text"/>

	<xsl:include href="../../common.xsl" />
	<xsl:include href="../../convert.xsl" />

   <xsl:template match="lzc:project">
<!-- Create the relationships -->
<xsl:text>-- Relationship Creation
</xsl:text>
      <xsl:for-each select="lzc:tables/lzc:table">
			<xsl:variable name="v_FKCount" select="count( .//lzc:index[ @type = $INDEXTYPE_FOREIGN ] )" />
         <xsl:if test="$v_FKCount &gt; 0">
            <xsl:variable name="object" select="@name" />
<xsl:text>
-- Table tbl</xsl:text><xsl:value-of select="@name" /><xsl:text> Fields
</xsl:text>
   <xsl:for-each select=".//lzc:index[ @type = $INDEXTYPE_FOREIGN ]">
		<xsl:variable name="foreignobject" select="@object" />
		<xsl:variable name="foreignkey" select="//lzc:table[ @name = $foreignobject ]//lzc:field[ lzc:indexes/lzc:index/@type = $INDEXTYPE_PRIMARY ]/@name" />
		<xsl:variable name="TABLE_NAME">
			<xsl:text>tbl</xsl:text>
			<xsl:value-of select="ancestor::lzc:table/@name" />
		</xsl:variable>
<xsl:text>IF ( NOT EXISTS( SELECT syso.id FROM sysconstraints sysc INNER JOIN sysobjects syso ON syso.id = sysc.id INNER JOIN sysobjects syso2 ON syso2.id = sysc.constid WHERE ( syso.name = '</xsl:text>
<xsl:value-of select="$TABLE_NAME" />
<xsl:text>' ) AND ( syso2.name = '</xsl:text>
<xsl:value-of select="@constraintname" />
<xsl:text>' ) ) )
</xsl:text>
<xsl:text>   ALTER TABLE </xsl:text>
		<xsl:value-of select="ancestor::lzc:project/@owner" />
		<xsl:text>.[</xsl:text>
		<xsl:value-of select="$TABLE_NAME" />
		<xsl:text>] ADD </xsl:text>
		<xsl:text>CONSTRAINT [</xsl:text><xsl:value-of select="@constraintname" />
		<xsl:text>] FOREIGN KEY ( [</xsl:text>
		<xsl:value-of select="ancestor::lzc:field/@dbname" />
		<xsl:text>] ) REFERENCES </xsl:text>
		<xsl:value-of select="ancestor::lzc:project/@owner" />
		<xsl:text>.[tbl</xsl:text>
		<xsl:value-of select="@object" />
		<xsl:text>] ( [</xsl:text>
		<xsl:value-of select="$foreignkey" />
		<xsl:text>] ) </xsl:text>
		<xsl:if test="@cascade-delete = 'True'">
			<xsl:text>ON DELETE CASCADE</xsl:text>
		</xsl:if>
		<xsl:text>
</xsl:text>
 		<xsl:text>GO

</xsl:text>
 		</xsl:for-each>
	</xsl:if>
	</xsl:for-each>

   </xsl:template>

</xsl:stylesheet>
  